﻿drop TYPE jobnumber_contractlist;
drop TYPE project_payment;
drop TYPE project_quotation;

create type project_quotation as (project_id int,quotation_id int);
create type project_payment as (project_id int,payement_id int);
create type jobnumber_contractlist as (project_id int,quotation_id int,payement_id int);

CREATE OR REPLACE FUNCTION fnproject_quotation_paymenttoclient(prj_id integer,second_id integer,check_quotaion_payment boolean=False)
  RETURNS setof record AS
$BODY$
declare
	r jobnumber_contractlist%rowtype;
	project_quotation_record project_quotation%rowtype;
	project_payment_record project_payment%rowtype;
	
	record_count record;
	project_payment_count integer:=0;
	project_quotation_count integer:=0;
	sql_project_payment text;
	sql_project_quoation_contracted text;
	count1 integer;
	Tinh boolean;
	dem integer;
begin
	sql_project_quoation_contracted ='Select
			kp.id as project_id,
			so_contracted.id as quotation_id,
			False as check_cal
		from
			kdvn_project kp
		left join
			sale_order so_contracted on ((kp.id = so_contracted.project_e_id and project_type=''E'') or (kp.id=so_contracted.project_m_id and project_type=''M'')) and so_contracted.state not in (''draft'',''cancel'') and so_contracted.contract_id is not null
		where kp.id = ' || $1::text;
	
	sql_project_payment='Select
		kpcr.project_id,
		kpfc.id as payment_id
	from
		kdvn_project_contract_rel kpcr
	left join
		kdvn_payment_from_client kpfc on kpcr.contract_id = kpfc.contract_id
	where kpfc.state not in (''draft'',''cancel'') and kpcr.project_id=' || $1::text;
	
	for record_count in execute 'select count(project_id) as num from (' || sql_project_payment || ') as fuckalias' loop
		project_payment_count = record_count.num;
	end loop;
	
	for record_count in execute 'select count(project_id) as num from (' || sql_project_quoation_contracted || ') as fuckalias' loop
		project_quotation_count = record_count.num;
	end loop;
	
	if project_payment_count is null then
		project_payment_count = 0;
	end if;
	if project_quotation_count is null then
		project_quotation_count = 0;
	end if;
	count1 = 1;
	if project_quotation_count>project_payment_count then		
		for project_quotation_record in execute sql_project_quoation_contracted loop
			r.project_id =project_quotation_record.project_id;
			--r.contract_id =project_quotation_record.contract_id;
			r.quotation_id =project_quotation_record.quotation_id;
			--r.project_id = null;
			r.payement_id = null;
			dem=1;
			Tinh = True;
			for project_payment_record in execute sql_project_payment loop
				if count1=dem and Tinh then
					r.project_id = project_payment_record.project_id;
					r.payement_id = project_payment_record.payement_id;
					count1 = count1+1;
					Tinh =False;
				end if;
				dem=dem+1;
			end loop;			
			return next r;
		end loop;
	else
		for project_payment_record in execute sql_project_payment loop
			r.project_id = project_payment_record.project_id;
			r.payement_id = project_payment_record.payement_id;
			--r.contract_id = project_payment_record.contract_id;
			r.quotation_id = null;	
			dem=1;
			Tinh = True;
			for project_quotation_record in execute sql_project_quoation_contracted loop
				if count1=dem and Tinh then
					--r.contract_id =project_quotation_record.contract_id;
					r.quotation_id =project_quotation_record.quotation_id;
					count1 = count1+1;
					Tinh =False;
				end if;
				dem=dem+1;
			end loop;
			return next r;
		end loop;
	end if;
end;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION fnproject_quotation_paymenttoclient(integer) OWNER TO openerp;
Select * from fnproject_quotation_paymenttoclient(148) as po(project_id int,quotation_id int,payement_id int);